/*
 * #%L
 * SwiftUIDataAccessLayer
 * $Id:$
 * $HeadURL:$
 * %%
 * Copyright (C) 2011 - 2012 Linkwithweb
 * %%
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as
 * published by the Free Software Foundation, either version 3 of the 
 * License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public 
 * License along with this program.  If not, see
 * <http://www.gnu.org/licenses/gpl-3.0.html>.
 * #L%
 */
/**
 * 
 */
package com.linkwithweb.products.swiftui.dao.impl;

import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.GregorianCalendar;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;

import com.linkwithweb.products.swiftui.adapter.FileUploadListener;
import com.linkwithweb.products.swiftui.adapter.RecordChangeRequest;
import com.linkwithweb.products.swiftui.adapter.RecordChangeRequestReceiver;
import com.linkwithweb.products.swiftui.context.ExcelFile;
import com.linkwithweb.products.swiftui.context.SystemContext;
import com.linkwithweb.products.swiftui.dao.PlatformDAO;
import com.linkwithweb.products.swiftui.dataobject.FrameworkApplication;
import com.linkwithweb.products.swiftui.dataobject.FrameworkReportColumn;
import com.linkwithweb.products.swiftui.ui.model.ProcessingInstructions;
import com.linkwithweb.products.swiftui.utils.GenericExcelReader;
import com.linkwithweb.products.swiftui.utils.ServiceLocator;
import com.linkwithweb.products.swiftui.vo.TableMetadata;

/**
 * @author ashwin kumar
 * 
 */
public class ComplexExcelToDBMigrator {

	@Autowired
	@Qualifier("recordChangeRequestReceiver")
	private RecordChangeRequestReceiver recordChangeRequestReceiver;

	@Autowired
	@Qualifier("fileUploadListener")
	private FileUploadListener fileUploadListener;

	private static final int SHEET_NUMBER = 0;
	private static final int HEADER_ROW_NUMBER = 5;

	/**
	 * @return
	 */
	public RecordChangeRequestReceiver getRecordChangeRequestReceiver() {
		if (recordChangeRequestReceiver == null) {
			recordChangeRequestReceiver = (RecordChangeRequestReceiver) ServiceLocator
					.getService("recordChangeRequestReceiver");
		}
		return recordChangeRequestReceiver;

	}

	/**
	 * @return
	 */
	public FileUploadListener getFileUploadListener() {
		if (fileUploadListener == null) {
			fileUploadListener = (FileUploadListener) ServiceLocator.getService("fileUploadListener");
		}
		return fileUploadListener;

	}

	/**
	 * @param fileName
	 * @param jdbcTemplate
	 * @param platformDAOImpl
	 */
	public void processFile(ExcelFile excelFile, JdbcTemplate jdbcTemplate, PlatformDAO platformDAOImpl) {
		List<String> sheets = new ArrayList<String>();

		if (StringUtils.containsIgnoreCase(excelFile.getUploadType(), "new")) {
			sheets.add("ALL - New");
		} else if (StringUtils.containsIgnoreCase(excelFile.getUploadType(), "update")) {
			sheets.add("ALL - Update");
		}

		/**
		 * Notify listeners on start of file processing...
		 */
		try {
			getFileUploadListener().fileProcessStarted(excelFile);
		} catch (Exception e) {
			SystemContext.getInstance().getUniqueMessage().set(
					"Exception while calling pre process Listeners :" + e.getLocalizedMessage());
			SystemContext.getInstance().getMessages().add(
					"Exception while calling pre process Listeners :" + e.getLocalizedMessage());
			// return;
		}

		for (String sheetName : sheets) {
			excelFile.getExcelTabs().clear();
			ProcessingInstructions instructions = new ProcessingInstructions();
			List<Map<String, Object>> excelData = null;
			/**
			 * Read Data tab.. For now we are reading 2nd sheet
			 */
			try {
				excelData = GenericExcelReader.readComplexDataSheet(excelFile, 7, sheetName, instructions);
				if (excelData == null) {
					continue;
				}

			} catch (Exception e) {
				continue;
			}
			if (excelFile == null) {
				continue;
			}

			/**
			 * Read Lov tab for now we are reading 3rd sheet
			 */
			GenericExcelReader.readLovSheet(excelFile, 1, "Drop Down Data", instructions);

			try {
				createTables(excelFile, instructions, jdbcTemplate);
			} catch (Exception e) {
				// e.printStackTrace();
			}

			int rowNumber = 9;
			for (Map<String, Object> dataRow : excelData) {
				try {
					/**
					 * If dataRow is blank then leave it...
					 */
					boolean isBlank = true;
					for (Object colValue : dataRow.keySet()) {
						if (colValue != null && StringUtils.isNotBlank(colValue.toString())
								&& !StringUtils.equalsIgnoreCase(colValue.toString(), "###.##")) {
							isBlank = false;
						}
					}

					if (isBlank) {
						continue;
					}

					String responseStatus = "FAILURE";
					try {
						RecordChangeRequest changeRequest = new RecordChangeRequest();
						changeRequest.setTableName(excelFile.getExcelTabs().get(0).getTableName());
						changeRequest.setRequestType(excelFile.getExcelType());
						changeRequest.setUserName(SystemContext.getInstance().getUserContext().getUserId());
						changeRequest.setAttributes(dataRow);
						responseStatus = (String) getRecordChangeRequestReceiver().recordChangeRequest(changeRequest);
					} catch (Exception e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					/**
					 * Store processing status in list...
					 */
					excelFile.getRowProcessingStatusList().add("For Row:" + rowNumber + "  :  " + responseStatus);

					insertData(rowNumber, responseStatus, dataRow, excelFile, jdbcTemplate, instructions);

					SystemContext.getInstance().getUniqueMessage().set(
							"Row :" + rowNumber + " successfully sent to DRM for processing");
					SystemContext.getInstance().getMessages().add(
							"Row :" + rowNumber + " successfully sent to DRM for processing");
				} catch (Exception e) {
					SystemContext.getInstance().getUniqueMessage().set(
							"Row :" + rowNumber + " Error processing record " + e.getLocalizedMessage());
					SystemContext.getInstance().getMessages().add(
							"Row :" + rowNumber + " Error processing record " + e.getLocalizedMessage());
				}

				rowNumber = rowNumber + 1;
			}

			/**
			 * Notify listeners on completion of file processing...
			 */
			try {
				getFileUploadListener().fileProcessCompleted(excelFile);
			} catch (Exception e) {
				SystemContext.getInstance().getUniqueMessage().set(
						"Exception while calling post process Listeners :" + e.getLocalizedMessage());
				SystemContext.getInstance().getMessages().add(
						"Exception while calling post process Listeners :" + e.getLocalizedMessage());
			}

		}
		/**
		 * Generate LOV Data in system
		 */
		/*
		 * MailUtils mailUtils =
		 * (MailUtils)ServiceLocator.getService("mailUtils");
		 * mailUtils.sendMailWithAttachments("ashwin@linkwithweb.com", "Hi",
		 * "Test", excelFile.getFileName(), excelFile.getFilePath(),
		 * "kumar@linkwithweb.com");
		 */

	}

	/**
	 * @param jdbcTemplate
	 * @return
	 */
	public static String getSchemaName(JdbcTemplate jdbcTemplate) {
		DatabaseMetaData meta;
		ResultSet rs = null;
		List<String> frameworkSchema = new ArrayList<String>();
		frameworkSchema.add("mysql");
		frameworkSchema.add("information_schema");
		frameworkSchema.add("automata");
		frameworkSchema.add("performance_schema");
		frameworkSchema.add("test");

		try {

			if (StringUtils.equalsIgnoreCase(SystemContext.getInstance().getUserContext().getFrameworkUser()
					.getListOfApplications().get(0).getDatasourceType(), "oracle")) {
				return SystemContext.getInstance().getUserContext().getFrameworkUser().getListOfApplications().get(0)
						.getDbUsername();
			}

			meta = jdbcTemplate.getDataSource().getConnection().getMetaData();

			rs = meta.getTables(null, null, null, new String[] { "TABLE" });
			FrameworkApplication app = SystemContext.getInstance().getUserContext().getFrameworkUser()
					.getListOfApplications().get(0);
			while (rs.next()) {
				TableMetadata metadata = new TableMetadata();
				String tableName = rs.getString("TABLE_NAME");
				String schemaName = rs.getString("TABLE_SCHEM");
				if (StringUtils.isBlank(schemaName)) {
					schemaName = rs.getString("TABLE_CAT");
				}

				if (frameworkSchema.contains(schemaName)) {
					continue;
				}
				return schemaName;
			}
			if (rs != null)
				rs.close();

			rs = meta.getSchemas();
			while (rs.next()) {
				String schemaName = rs.getString("TABLE_SCHEM");
				if (StringUtils.isBlank(schemaName)) {
					try {
						schemaName = rs.getString("TABLE_CAT");
					} catch (Exception e) {
						// TODO: handle exception
					}
				}
				if (StringUtils.isBlank(schemaName)) {
					try {
						schemaName = rs.getString("TABLE_CATALOG");
					} catch (Exception e) {
						// TODO: handle exception
					}
				}

				if (frameworkSchema.contains(schemaName)) {
					continue;
				}
				return schemaName;
			}
			if (rs != null)
				rs.close();

			rs = meta.getCatalogs();
			while (rs.next()) {
				String schemaName = "";
				if (StringUtils.isBlank(schemaName)) {
					schemaName = rs.getString("TABLE_CAT");
				}
				if (StringUtils.isBlank(schemaName)) {
					try {
						schemaName = rs.getString("TABLE_CATALOG");
					} catch (Exception e) {
						// TODO: handle exception
					}
				}

				if (frameworkSchema.contains(schemaName)) {
					continue;
				}
				return schemaName;
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					// e.printStackTrace();
				}
		}
		return null;
	}


	/**
	 * @param columnNames
	 * @param instructions
	 */
	public static void createTables(ExcelFile fileHandler, ProcessingInstructions instructions,
			JdbcTemplate jdbcTemplate) {
		String schemaName = getSchemaName(jdbcTemplate);
		StringBuffer query = new StringBuffer();
		query.setLength(0);
		query
				.append("CREATE TABLE ")
				.append(schemaName)
				.append(".")
				.append(fileHandler.getExcelTabs().get(0).getTableName())
				.append(
						"(UNIQUE_KEY int(10) unsigned NOT NULL AUTO_INCREMENT,RECORD_STATE VARCHAR(200),UPLOADED_BY VARCHAR(25),UPLOAD_DATE DATETIME,ROW_NUMBER int(11)");

		int index = 0;
		for (String columnName : fileHandler.getExcelTabs().get(0).getColumnNames()) {
			if (StringUtils.equalsIgnoreCase(columnName, "EMPTY COLUMN")) {
				index = index + 1;
				continue;
			}

			query.append(",");
			query.append(FrameworkReportColumn.convertAndGetGenericToDBColumnName(columnName)).append(" ");
			/**
			 * Based On Column Type Generate ColumnType
			 */
			generateColumTypeString(fileHandler, query, index);

			index = index + 1;
		}

		query.append(",PRIMARY KEY (`UNIQUE_KEY`)");

		query.append(")");

		/**
		 * Now Create tables in DB
		 */
		if (instructions.isProcessFile()) {
			jdbcTemplate.update(query.toString());
		}

	}

	/**
	 * @param fileHandler
	 * @param query
	 * @param index
	 */
	private static void generateColumTypeString(ExcelFile fileHandler, StringBuffer query, int index) {
		if (StringUtils.equalsIgnoreCase("Text Box", fileHandler.getExcelTabs().get(0).getColumnTypes().get(index)
				.trim())) {
			query.append(" VARCHAR(90) ");
		} else if (StringUtils.equalsIgnoreCase("Drop Down Box", fileHandler.getExcelTabs().get(0).getColumnTypes()
				.get(index).trim())) {
			query.append(" VARCHAR(40) ");
		} else if (StringUtils.equalsIgnoreCase("Datetime", fileHandler.getExcelTabs().get(0).getColumnTypes().get(
				index).trim())) {
			query.append(" datetime ");
		} else if (StringUtils.equalsIgnoreCase("Text Box", fileHandler.getExcelTabs().get(0).getColumnTypes().get(
				index))) {
			query.append(" VARCHAR(90) ");
		} else if (StringUtils.equalsIgnoreCase("Text Box", fileHandler.getExcelTabs().get(0).getColumnTypes().get(
				index))) {
			query.append(" VARCHAR(90) ");
		} else {
			query.append(" VARCHAR(90) ");
		}
	}

	/**
	 * @param rowNumber
	 * @param responseStatus
	 * @param dataRow
	 * @param fileHandler
	 * @param jdbcTemplate
	 * @param instructions
	 */
	private static void insertData(int rowNumber, String responseStatus, Map<String, Object> dataRow,
			ExcelFile fileHandler, JdbcTemplate jdbcTemplate, ProcessingInstructions instructions) {
		StringBuffer query = new StringBuffer();
		query.setLength(0);
		String schemaName = getSchemaName(jdbcTemplate);
		query.append("INSERT INTO ").append(schemaName).append(".").append(
				fileHandler.getExcelTabs().get(0).getTableName()).append(
				"(RECORD_STATE,UPLOADED_BY,UPLOAD_DATE,ROW_NUMBER");

		for (String columnName : fileHandler.getExcelTabs().get(0).getColumnNames()) {
			if (StringUtils.equalsIgnoreCase(columnName, "EMPTY COLUMN")) {
				continue;
			}
			query.append(",");
			query.append(FrameworkReportColumn.convertAndGetGenericToDBColumnName(columnName));
		}

		List arguments = new ArrayList();
		query.append(") VALUES (?,?,?,?");
		if (StringUtils.isNotBlank(responseStatus) && responseStatus.length() > 200) {
			arguments.add(responseStatus.substring(0, 199));
		} else {
			arguments.add(responseStatus);
		}

		arguments.add(SystemContext.getInstance().getUserContext().getPassword());
		arguments.add((new GregorianCalendar()).getTime());
		arguments.add(rowNumber);
		int index = 0;
		for (String columnName : fileHandler.getExcelTabs().get(0).getColumnNames()) {
			if (StringUtils.equalsIgnoreCase(columnName, "EMPTY COLUMN")) {
				index = index + 1;
				continue;
			}
			query.append(",");

			if (dataRow.get(columnName) != null && dataRow.get(columnName) instanceof String) {
				String dataValue = (String) dataRow.get(columnName);
				if (StringUtils.equalsIgnoreCase("Drop Down Box", fileHandler.getExcelTabs().get(0).getColumnTypes()
						.get(index).trim())) {
					if (dataValue.length() >= 40) {
						dataValue = dataValue.substring(0, 39);
					}
				} else {
					if (dataValue.length() > 90) {
						dataValue = dataValue.substring(0, 89);
					}
				}

				arguments.add(dataValue);
			} else {
				arguments.add(dataRow.get(columnName));
			}
			index = index + 1;
			query.append("?");
		}
		query.append(")");

		jdbcTemplate.update(query.toString(), arguments.toArray());

	}

	/**
	 * @param value
	 * @return
	 */
	private static String getStringValue(Object value) {
		String response = null;
		if (value != null) {
			if (value instanceof Double) {
				Double d = (Double) value;
				response = String.valueOf(d.longValue());
			} else {
				response = value.toString();
			}
		}
		if (response != null) {
			response = StringEscapeUtils.escapeSql(response);
		}
		return response;
	}
}
